package com.carple.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.carple.dto.Board;
import com.carple.dto.BoardComment;

public class BoardDao {
	
	public void insertBoard(Board board) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			conn.setAutoCommit(false);
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO board (title, header, emailid, content, groupno, step) VALUES (?,?,?,?,-1,1)");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getHeader());
			pstmt.setString(3, board.getEmailid());
			pstmt.setString(4, board.getContent());
			
			pstmt.executeUpdate();
			pstmt.close();
			
			
			////groupno에 boardno를 넣기 위한 작업
			sql.delete(0, sql.length());
			//LAST_INSERT_ID(): 현재 세션에서 마지막으로 처리된 자동 증가값
			sql.append("UPDATE board SET groupno = LAST_INSERT_ID() WHERE boardno = LAST_INSERT_ID()");
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.executeUpdate();
			
			conn.commit();//트랜젝션 완성.두가지 작업(INSERT,UPDATE)을 모두 완료하기 위해 사용.
			
			////groupno에 boardno를 넣기 위한 작업 끝
			
		} catch (Exception ex) {
			try{conn.rollback();}catch(Exception ex2){}
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try {conn.setAutoCommit(true);}catch(Exception ex){}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
///////////////////////////////////////////////////////////////

	public List<Board> getBoardList(int startRow, int count,String header) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<Board> boards = new ArrayList<Board>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			System.out.println("DAO"+header);
			if(header==null){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, groupno, step, depth, header ")
				.append("FROM board ORDER BY groupno DESC, step ASC ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}else if(header.equals("walker")){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, groupno, step, depth, header ")
				.append("FROM board WHERE header = 'walker' ORDER BY groupno DESC, step ASC ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}else if(header.equals("driver")){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, groupno, step, depth, header ")
				.append("FROM board WHERE header = 'driver' ORDER BY groupno DESC, step ASC ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}
			
			
			
			while(rs.next()){
				Board board = new Board();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setEmailid(rs.getString(3));
				//board.setContent(rs.getString(4));
				board.setReadCount(rs.getInt(4));
				board.setRegDate(rs.getDate(5));
				board.setDeleted(rs.getBoolean(6));
				board.setGroupNo(rs.getInt(7));
				board.setStep(rs.getInt(8));
				board.setDepth(rs.getInt(9));
				board.setHeader(rs.getString(10));
				
				
				boards.add(board);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return boards;
	}
	
	public Board viewContent(String str) {
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		Board board = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			sql.append("SELECT boardno, title, emailid, content, readcount, regdate, groupno, step, depth, header FROM board WHERE boardno = ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, str);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				board = new Board();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setEmailid(rs.getString(3));
				board.setContent(rs.getString(4));
				board.setReadCount(rs.getInt(5));
				board.setRegDate(rs.getDate(6));
				board.setGroupNo(rs.getInt(7));
				board.setStep(rs.getInt(8));
				board.setDepth(rs.getInt(9));
				board.setHeader(rs.getString(10));
				//댓글 조회
				sql.delete(0, sql.length());//StringBuilder 비우기
				sql.append("SELECT commentno, boardno, ")
				   .append("	   emailid, content, regdate ")
				   .append("FROM boardcomment ")
				   .append("WHERE boardno = ?");
				pstmt2 = conn.prepareStatement(sql.toString());
				pstmt2.setString(1, str);
				rs2 = pstmt2.executeQuery();
				ArrayList<BoardComment> comments 
					= new ArrayList<BoardComment>();
				while (rs2.next()) {
					BoardComment comment = new BoardComment();
					comment.setCommentNo(rs2.getInt(1));
					comment.setBoardNo(rs2.getInt(2));
					comment.setEmailId(rs2.getString(3));
					comment.setContent(rs2.getString(4));
					comment.setRegDate(rs2.getDate(5));
					comments.add(comment);
				}
				board.setComments(comments);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return board;
	}
	
	
	public int getBoardCount(String header) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		int count = 0;
		
		try {
			conn = ConnectionHelper.getConnection();
			//select count(*) from board where header = 'walker';
			if(header == null){
				String sql = ("SELECT COUNT(*) FROM board");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}else if(header.equals("walker")){
				String sql = ("select count(*) from board where header = 'walker'");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}else if(header.equals("driver")){
				String sql = ("select count(*) from board where header = 'driver'");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}

			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return count;
	}

	public void deleteBoard(int boardNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = "UPDATE board SET deleted = true WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			System.out.println(boardNo);
			pstmt.executeUpdate();	
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		
	}

	public int updateBoard(Board board) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE board SET title = ?, content = ? WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getContent());
			pstmt.setInt(3, board.getBoardNo());
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
		
	}
	public int insertReply(Board board) {
		Connection conn = null;		
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			//댓글을 작성할 대상글의 정보를 조회
			Board temp = viewContent(String.format("%d", board.getBoardNo()));
			
			//영향을 받는 기존 글들의 논리적인 순서 번호 변경
			conn = ConnectionHelper.getConnection();			
			String sql =
				"UPDATE board " + 
				"SET step = step + 1 " + 
				"WHERE step > ? AND groupno = ?";
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, temp.getStep());//step 번호
			pstmt.setInt(2, temp.getGroupNo());//group 번호
			pstmt.executeUpdate();
			pstmt.close();
			
			//댓글 삽입
			StringBuilder sql2 = new StringBuilder(256);
			sql2.append("INSERT INTO board ");
			sql2.append("(title, emailid, content, deleted, groupno, step, depth) ");
			sql2.append("VALUES (?, ?, ?, false, ?, ?, ?)");
			pstmt = conn.prepareStatement(sql2.toString());
			pstmt.setString(1, board.getTitle());//제목
			pstmt.setString(2, board.getEmailid());//작성자
			pstmt.setString(3, board.getContent());//내용
			pstmt.setInt(4, temp.getGroupNo());//step
			pstmt.setInt(5, temp.getStep() + 1);//step
			pstmt.setInt(6, temp.getDepth() + 1);//depth
			
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
		
	}
	
	public void increaseBoardReadCount(int number) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = "UPDATE board SET readcount = readcount + 1 WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, number);
			System.out.println(pstmt.toString());
			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
	}
	
public void insertComment(BoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"INSERT INTO boardcomment " +
				"(boardno, emailid, content) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, comment.getBoardNo());
			pstmt.setString(2, comment.getEmailId());
			pstmt.setString(3, comment.getContent());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void updateComment(BoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"UPDATE boardcomment " +
				"SET content = ?" +
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, comment.getContent());
			pstmt.setInt(2, comment.getCommentNo());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void deleteComment(int commentNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"DELETE FROM boardcomment " +				
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setInt(1, commentNo);

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}

}
